from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, String
from sqlalchemy.orm import sessionmaker

engine = create_engine('sqlite+pysqlite:///test.db', echo=False)
Base = declarative_base()
Session = sessionmaker(engine)
session = Session()


class User(Base):
    __tablename__ = 'user'
    _id = Column(Integer, autoincrement=True, primary_key=True)
    name = Column(String(20))
    age = Column(String(20))
    edu = Column(String(20))


def initdb():
    Base.metadata.create_all(engine)


def dropdb():
    Base.metadata.drop_all(engine)


def adduser():
    session.add(User(name='joe', age=10, edu='master'))
    session.commit()


def updateusername(myid, name):
    session.query(User).filter(User._id == myid).update({'name': name})
    session.commit()


def deleteuser(myid):
    result = session.query(User).filter(User._id == myid).delete()
    session.commit()
    return result


def queryuser():
    result = session.query(User).all()
    session.commit()
    return result

